﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using NUS.ISS.ARS.BusinessEntity;
using NUS.ISS.ARS.BusinessEntity.ViewModels;
using NUS.ISS.ARS.Common;
using System.Data.Entity;
using System.Transactions;

namespace NUS.ISS.ARS.BusinessComponent
{
    public class FlightBookingBC : BaseBC
    {
        public IEnumerable<CancelBookingTripView> IEnumGetFlightBookings()
        {
            IEnumerable<CancelBookingTripView> flightbookings = null;

            using (var ent = new Entities())
            {
                flightbookings = ent.FlightBookings.Where(b => b.IsCancelled == false)
                                         .Include(b => b.FlightBookingTrips)
                                          .Include(b => b.Passengers)
                                         .Include(b => b.FlightBookingTrips)
                                      .Select(t => new CancelBookingTripView()
                        {
                            FlightBookingId = t.FlightBookingId,
                            CustomerId = t.CustomerId,
                            FirstName = t.Customer.FirstName,
                            LastName = t.Customer.LastName,
                            CustomerName = t.Customer.FirstName + " " + t.Customer.LastName,
                            ReferenceNumber = t.ReferenceNumber,
                            IsCancelled = t.IsCancelled,
/*
                            FlightBookingTripId = t.FlightBookingTripId,
                            FlightScheduleId = t.FlightBookingTrip.FlightScheduleId,
                            FlightDate = t.FlightBookingTrip.FlightDate,
                            FlightNumber = t.FlightBookingTrip.FlightSchedule.FlightNumber,
                            DepartureTime = t.FlightBookingTrip.FlightSchedule.DepartureTime,
                            ArrivalTime = t.FlightBookingTrip.FlightSchedule.ArrivalTime,
                            DepartureCityCode = t.FlightBookingTrip.FlightSchedule.Airport.City.CityCode,
                            ArrivalCityCode = t.FlightBookingTrip.FlightSchedule.Airport1.City.CityCode,
                            FromCity = t.FlightBookingTrip.FlightSchedule.Airport.City.CityName,
                            ToCity = t.FlightBookingTrip.FlightSchedule.Airport1.City.CityName,
                            FromAirport = t.FlightBookingTrip.FlightSchedule.Airport.AirportName,
                            ToAirport = t.FlightBookingTrip.FlightSchedule.Airport1.AirportName,
                */
                        }
                
                ).ToList();
                 return flightbookings;
            }
        }
     
        public IEnumerable<FlightBookingTripView> GetFlightBookingsByRefNoAndName(string refNo, string firstName, string lastName)
        {
            FlightBooking fb = null;
            DateTime today = DateTime.Now.Date;

            using (var ent = new Entities())
            {
                fb = ent.FlightBookings.Where(b => b.ReferenceNumber == refNo)
                                         .Include(b => b.Customer).Where(b => b.Customer.FirstName == firstName
                                                                         && b.Customer.LastName == lastName
                                                                         && b.IsCancelled == false)
                                         .Include(b => b.FlightBookingTrips
                                            .Select(t => t.FlightBookingTripDetails))
                                         .Include(b => b.FlightBookingTrips
                                             .Select(t => t.FlightSchedule)).FirstOrDefault();
                                   

                IEnumerable<FlightBookingTripView> trips = null;

                if (fb != null)
                {
                    trips = fb.FlightBookingTrips
                                    .Select(t => new FlightBookingTripView()
                                    {
                                        FlightBookingTripId = t.FlightBookingTripId,
                                        ReferenceNumber = fb.ReferenceNumber,
                                        FlightDate = t.FlightDate,
                                        FlightScheduleId = t.FlightSchedule.FlightScheduleId,
                                        FlightNo = t.FlightSchedule.FlightNumber,
                                        DepartureTime = t.FlightSchedule.DepartureTime,
                                        DepartureCityCode = t.FlightSchedule.Airport.City.CityCode,
                                        FromCityId = t.FlightSchedule.Airport.City.CityId,
                                        FromCity = t.FlightSchedule.Airport.City.CityName,
                                        ArrivalTime = t.FlightSchedule.ArrivalTime,
                                        ArrivalCityCode = t.FlightSchedule.Airport1.City.CityCode,
                                        ToCityId = t.FlightSchedule.Airport1.City.CityId,
                                        ToCity = t.FlightSchedule.Airport1.City.CityName,
                                        TotalPrice = t.FlightBookingTripDetails.Sum(d => d.Price),
                                        TotalAdultPrice = t.FlightBookingTripDetails.Where(d => d.Passenger.Type == (byte)Constants.PassengerType.Adult).Sum(d => d.Price),
                                        TotalChildPrice = t.FlightBookingTripDetails.Where(d => d.Passenger.Type == (byte)Constants.PassengerType.Child).Sum(d => d.Price),
                                        Surcharge = t.FlightBookingTripDetails.Sum(d => d.Surcharge) == null ? 0 : t.FlightBookingTripDetails.Sum(d => d.Surcharge),

                                        BookingTripDetailViews = t.FlightBookingTripDetails.Select(d => new BookingTripDetailView() {
                                                                                                    BookingTripDetailId = d.FlightBookingTripDetailId,
                                                                                                    PassengerId = d.PassengertId,
                                                                                                    Price = d.Price,
                                                                                                    Surcharge = d.Surcharge
                                                                                                }).ToList()
                                    }).Where(t => t.FlightDate > today).ToList();
                }

                return trips;
            }
        }
        public CancelBookingTripView GetFlightBookingsByFlightBooking(long id)
        {
            CancelBookingTripView flightbooking = null;
            using (var ent = new Entities())
            {
                flightbooking = ent.FlightBookings.Where(a => a.FlightBookingId == id)
                        .Select(t => new CancelBookingTripView()
                        {
                            FlightBookingId = t.FlightBookingId,
                            CustomerId = t.CustomerId,
                            PaymentMode = t.PaymentMode,
                            CreatedDateTime = t.CreatedDateTime,
                            CreatedBy = t.CreatedBy,
                            LastUpdatedDateTime = t.LastUpdatedDateTime,
                            LastUpdatedBy = t.LastUpdatedBy,
                            TimeStamp = t.TimeStamp,
                            ReferenceNumber = t.ReferenceNumber,
                            IsCancelled = t.IsCancelled,
                        }).OrderBy(t => t.ReferenceNumber).FirstOrDefault();
            }
            return flightbooking;           
        }
        
        public FlightBookingTripView GetFlightBookingsById(long id)
        {
            FlightBookingTripView flightbookingtripview = null;
            if (!(id == null || id == 0))
            {
                using (var ent = new Entities())
                {
                    flightbookingtripview = ent.FlightBookingTrips.Where(a => a.FlightBookingTripId == id)
                                                 .Include(a => a.FlightBookingTripDetails)
   
                            .Select(t => new FlightBookingTripView()
                            {
                                FlightBookingTripId = t.FlightBookingTripId,
                                ReferenceNumber = t.FlightBooking.ReferenceNumber,
                                FlightDate = t.FlightDate,
                                DepartureTime = t.FlightSchedule.DepartureTime,
                                DepartureCityCode = t.FlightSchedule.Airport.City.CityCode,
                                ArrivalTime = t.FlightSchedule.ArrivalTime,
                                ArrivalCityCode = t.FlightSchedule.Airport1.City.CityCode,
                               // TotalPrice = t.FlightBookingTripDetails.Sum(d => d.Price),
                               // TotalAdultPrice = t.FlightBookingTripDetails.Where(d => d.Passenger.Type == (byte)Constants.PassengerType.Adult).Sum(d => d.Price),
                              //  TotalChildPrice = t.FlightBookingTripDetails.Where(d => d.Passenger.Type == (byte)Constants.PassengerType.Child).Sum(d => d.Price),
                               // Surcharge = t.FlightBookingTripDetails.Sum(d => d.Surcharge),
                                FromCityId = t.FlightSchedule.Airport.City.CityId,
                                ToCityId = t.FlightSchedule.Airport1.City.CityId,
                                FromAirportName = t.FlightSchedule.Airport.AirportName,
                                ToAirportName = t.FlightSchedule.Airport1.AirportName,
                                FlightScheduleId = t.FlightSchedule.FlightScheduleId,
                                FlightNo = t.FlightSchedule.FlightNumber,
                                PassengerName = t.FlightBooking.Customer.FirstName + " " + t.FlightBooking.Customer.LastName,
                                IsCancelled = t.FlightBooking.IsCancelled,
                                
                                BookingTripDetailViews = t.FlightBookingTripDetails.Select(d => new BookingTripDetailView()
                                {
                                    BookingTripDetailId = d.FlightBookingTripDetailId,
                                    PassengerId = d.PassengertId,
                                    Passengername = d.Passenger.FirstName + " " + d.Passenger.LastName,
                                 //   Price = d.Price,
                                 //   Surcharge = d.Surcharge
                                }).ToList()
                            }).OrderBy(a => a.ReferenceNumber).FirstOrDefault();
                }
            }
            return flightbookingtripview;

        }
        public SaveResult SaveChangedBooking(CancelBookingTripView flightbooking)
        {
            try{
                 using (var ent = new Entities())
                  {
                      var FlightBooking = ent.FlightBookings.Single(b => b.FlightBookingId == flightbooking.FlightBookingId);
                      if (FlightBooking != null)
                        {
                            FlightBooking.FlightBookingId = flightbooking.FlightBookingId;
                            FlightBooking.CustomerId = flightbooking.CustomerId;
                            FlightBooking.PaymentMode = flightbooking.PaymentMode;
                            FlightBooking.CreatedDateTime = flightbooking.CreatedDateTime;
                            FlightBooking.CreatedBy = flightbooking.CreatedBy;
                            FlightBooking.LastUpdatedDateTime = flightbooking.LastUpdatedDateTime;
                            FlightBooking.LastUpdatedBy = flightbooking.LastUpdatedBy;
                            FlightBooking.TimeStamp = flightbooking.TimeStamp;
                            FlightBooking.ReferenceNumber = flightbooking.ReferenceNumber;
                            FlightBooking.IsCancelled = flightbooking.IsCancelled;

                            if (FlightBooking.IsValid)
                            {
                                ent.SaveChanges();
                            }
                            else
                            {
                                return new SaveResult()
                                {
                                    Status = Constants.SaveStatus.Failed,
                                    Message = FlightBooking.ErrorMessage
                                };
                            }
                        }
                    }
            }
            catch (Exception ex)
            {
                string s = ex.Message;
                //TODO: log error
                SetError("Exception occurred.");
                return new SaveResult()
                {
                    Status = Constants.SaveStatus.Failed,
                    Message = ErrorMessage
                };
            }

            return new SaveResult()
            {
                Status = Constants.SaveStatus.Success,
                Message = ""
            };
        }



        public List<long> FlightBookingTripIdByFlightBookingId(long flightbookingid)
        {
            List<long> flightbookingids = new List<long>();
            List<FlightBookingTripView> flightbookingtripviews = null;
            using (var ent = new Entities())
            {
                flightbookingtripviews = ent.FlightBookingTrips.Where(a => a.FlightBookingId == flightbookingid)                                  
                        .Select(t => new FlightBookingTripView()
                        {
                            FlightBookingTripId=t.FlightBookingTripId,
                        }).ToList();
            }
            foreach (var flightbookingtripview in flightbookingtripviews)
            {
                flightbookingids.Add(flightbookingtripview.FlightBookingTripId);
            }
            return flightbookingids;
        }
   
        public IEnumerable<CancelBookingTripView> GetFlightBookingsByRefNoAndFirstNameAndLastName(string refNo, string firstName, string lastName)
        {
            IEnumerable<CancelBookingTripView> flightbookings = null;
            using (var ent = new Entities())
            {
                flightbookings = ent.FlightBookings.Where(a => a.ReferenceNumber.Contains(refNo)
                                        && a.Customer.FirstName.Contains(firstName)
                                        && a.Customer.LastName.Contains(lastName) && a.IsCancelled == false)
                                         .Include(b => b.FlightBookingTrips)
                                          .Include(b => b.Passengers)
                                         .Include(b => b.FlightBookingTrips)
                                      .Select(t => new CancelBookingTripView()
                                      {
                                          FlightBookingId = t.FlightBookingId,
                                          CustomerId = t.CustomerId,
                                          FirstName = t.Customer.FirstName,
                                          LastName = t.Customer.LastName,
                                          CustomerName = t.Customer.FirstName + " " + t.Customer.LastName,
                                          ReferenceNumber = t.ReferenceNumber,
                                          IsCancelled = t.IsCancelled,
                                      }

                ).ToList();
                return flightbookings;
            }
        }
        public IEnumerable<FlightBookingTripView> GetAllFlightBookings()
        {
            List<FlightBooking> fb = null;
            DateTime today = DateTime.Now.Date;

            using (var ent = new Entities())
            {
                fb = ent.FlightBookings.Where(b => b.IsCancelled == false)
                                         .Include(b => b.FlightBookingTrips
                                            .Select(t => t.FlightBookingTripDetails))
                                          .Include(b => b.Passengers
                                            .Select(t => t.FlightBookings))
                                         .Include(b => b.FlightBookingTrips
                                             .Select(t => t.FlightSchedule)).ToList();


                IEnumerable<FlightBookingTripView> trips = null;
                List<FlightBookingTripView> l = new List<FlightBookingTripView>();
                if (fb != null)
                {
                    foreach (var singlefb in fb)
                    {
                        trips = singlefb.FlightBookingTrips

                                    .Select(t => new FlightBookingTripView()
                                    {
                                        FlightBookingTripId = t.FlightBookingTripId,
                                        ReferenceNumber = singlefb.ReferenceNumber,
                                        FlightDate = t.FlightDate,
                                        FlightScheduleId = t.FlightSchedule.FlightScheduleId,
                                        DepartureTime = t.FlightSchedule.DepartureTime,
                                        DepartureCityCode = t.FlightSchedule.Airport.City.CityCode,
                                        FromCityId = t.FlightSchedule.Airport.City.CityId,
                                        ArrivalTime = t.FlightSchedule.ArrivalTime,
                                        ArrivalCityCode = t.FlightSchedule.Airport1.City.CityCode,
                                        ToCityId = t.FlightSchedule.Airport1.City.CityId,
                                        TotalPrice = t.FlightBookingTripDetails.Sum(d => d.Price),
                                        TotalAdultPrice = t.FlightBookingTripDetails.Where(d => d.Passenger.Type == (byte)Constants.PassengerType.Adult).Sum(d => d.Price),
                                        TotalChildPrice = t.FlightBookingTripDetails.Where(d => d.Passenger.Type == (byte)Constants.PassengerType.Child).Sum(d => d.Price),
                                        Surcharge = t.FlightBookingTripDetails.Sum(d => d.Surcharge),
                                        FlightNo = t.FlightSchedule.FlightNumber,
                                        PassengerName = t.FlightBooking.Customer.FirstName + " " + t.FlightBooking.Customer.LastName,
                                        BookingTripDetailViews = t.FlightBookingTripDetails.Select(d => new BookingTripDetailView()
                                        {
                                            BookingTripDetailId = d.FlightBookingTripDetailId,
                                            PassengerId = d.PassengertId,
                                            Price = d.Price,
                                            Surcharge = d.Surcharge
                                        }).ToList()
                                    }).Where(t => t.FlightDate > today).ToList();

                        foreach (var trip in trips)
                        {
                            l.Add(trip);
                        }

                    }

                }

                return l;
            }
        }     
        
        public IEnumerable<City> GetAllFlightCities()
        {
            IEnumerable<City> cities = null;

            //get all cities that have flights running
            using (var ent = new Entities())
            {
                cities = ent.Cities.Where(c => c.Airports.Any(a => a.FlightSchedules.Any(s => s.IsDeleted == false))).OrderBy(c => c.CityName).ToList();
            }

            return cities;
        }

        /*  asumption: flights are daily as per flight schedules
         *  
         */
        public IEnumerable<FlightBookingTripView> SearchFlights(ReservationViewObject rsvData)
        {
            DateTime today = DateTime.Now;
            if (rsvData.DepartDate <= today)
            {
                SetError("Depart date cannot be today or earlier.");
            }

            if (rsvData.ReturnDate < rsvData.DepartDate)
            {
                SetError("Return date cannot be less than depart date.");
            }

            IEnumerable<FlightBookingTripView> trips = null;

            using (var ent = new Entities())
            {
                    trips = ent.FlightSchedules.Where(s => s.Airport.CityId == rsvData.FromCityId
                                                && s.Airport1.CityId == rsvData.ToCityId
                                                && ( s.FlightTripStatus.Where(ts => ts.FlightDate == rsvData.DepartDate).Count() == 0
                                                    || s.FlightTripStatus.Any(ts => ts.FlightDate == rsvData.DepartDate && ts.NoOfBookedSeats < s.Aircraft.MaxSeat))) //no entry or not yet fully booked
                                                .Select(s => new FlightBookingTripView()
                                                { 
                                                    FlightScheduleId = s.FlightScheduleId,
                                                    FlightNo = s.FlightNumber,
                                                    FlightDate = rsvData.DepartDate,
                                                    DepartureTime = s.DepartureTime,                                                                                                                
                                                    DepartureCityCode = s.Airport.City.CityCode,
                                                    FromCityId = s.Airport.City.CityId,
                                                    FromCity = s.Airport.City.CityName,
                                                    ArrivalTime = s.ArrivalTime,
                                                    ArrivalCityCode = s.Airport1.City.CityCode,
                                                    ToCityId = s.Airport1.City.CityId,
                                                    ToCity = s.Airport1.City.CityName,
                                                    TotalPrice = s.AdultFare * rsvData.NumOfAdult + s.ChildFare * rsvData.NumOfChild,
                                                    TotalAdultPrice = s.AdultFare * rsvData.NumOfAdult,
                                                    TotalChildPrice = s.ChildFare * rsvData.NumOfChild,
                                                    Surcharge = s.Surcharges.Sum(sc => sc.Price * (rsvData.NumOfAdult + rsvData.NumOfChild)) //can have more than one surcharges per flight                                                    
                                                }).OrderBy(s => s.DepartureTime).ToList();
                
                if (rsvData.IsRoundTrip)
                {
                    IEnumerable<FlightBookingTripView> trips2 = null;
                    trips2 = ent.FlightSchedules.Where(s => s.Airport1.CityId == rsvData.FromCityId
                                                    && s.Airport.CityId == rsvData.ToCityId
                                                    && ( s.FlightTripStatus.Where(ts => ts.FlightDate == rsvData.DepartDate).Count() == 0
                                                        || s.FlightTripStatus.Any(ts => ts.FlightDate == rsvData.DepartDate && ts.NoOfBookedSeats < s.Aircraft.MaxSeat))) //no entry or not yet fully booked
                                                    .Select(s => new FlightBookingTripView()
                                                    {
                                                        FlightScheduleId = s.FlightScheduleId,
                                                        FlightNo = s.FlightNumber,
                                                        FlightDate = rsvData.ReturnDate,
                                                        DepartureTime = s.DepartureTime,
                                                        FromCityId = s.Airport.City.CityId,
                                                        FromCity = s.Airport.City.CityName,
                                                        DepartureCityCode = s.Airport.City.CityCode,
                                                        ArrivalTime = s.ArrivalTime,
                                                        ArrivalCityCode = s.Airport1.City.CityCode,
                                                        ToCityId = s.Airport1.City.CityId,
                                                        ToCity = s.Airport1.City.CityName,
                                                        TotalPrice = s.AdultFare * rsvData.NumOfAdult + s.ChildFare * rsvData.NumOfChild,
                                                        TotalAdultPrice = s.AdultFare * rsvData.NumOfAdult,
                                                        TotalChildPrice = s.ChildFare * rsvData.NumOfChild,
                                                        Surcharge = s.Surcharges.Sum(sc => sc.Price * (rsvData.NumOfAdult + rsvData.NumOfChild)) //can have more than one surcharges per flight
                                                    }).OrderBy(s => s.DepartureTime).ToList();

                    trips = trips.Union(trips2);
                }                
            }

            return trips;
        }

        public List<int> GetNumOfPassengersArrayByBookingRefNo(string refNo)
        {
            List<int> passengerNums = new List<int>();
            FlightBooking fb = null;

            using(var ent = new Entities())
            {
                fb = ent.FlightBookings.Where(b => b.ReferenceNumber == refNo).FirstOrDefault();
                if (fb != null)
                {
                    passengerNums.Add(fb.Passengers.Where(p => p.Type == (byte)Constants.PassengerType.Adult).Count());
                    passengerNums.Add(fb.Passengers.Where(p => p.Type == (byte)Constants.PassengerType.Child).Count());                   
                }                
            }

            return passengerNums;
        }
        
        /*  Calculate price summary for the trips included in the booking
         *  For manage booking, if the total amount due for changed trip/s is bigger, set the total due to the difference
         *  else, set all to zero.
         */
        public PriceSummaryView GetPriceSummary(IEnumerable<FlightBookingTripView> trips, bool isManageBooking)
        {
            PriceSummaryView ps = new PriceSummaryView();

            var newTrips = trips.Where(t => t.ToBeChanged != true);

            foreach (var trip in newTrips)
            {
                ps.AdultPrice += trip.TotalAdultPrice;
                ps.ChildPrice += trip.TotalChildPrice;
                ps.TotalPrice += trip.TotalPrice;
                ps.Surcharge += trip.Surcharge == null ? 0.0M : (decimal)trip.Surcharge;
            }

            ps.TotalDue = ps.TotalPrice + ps.Surcharge;

            if (isManageBooking)
            {
                PriceSummaryView ps_old = new PriceSummaryView();

                var oldTrips = trips.Where(t => t.ToBeChanged == true);

                foreach (var trip in oldTrips)
                {
                    ps_old.AdultPrice += trip.TotalAdultPrice;
                    ps_old.ChildPrice += trip.TotalChildPrice;
                    ps_old.TotalPrice += trip.TotalPrice;
                    ps_old.Surcharge += trip.Surcharge == null ? 0.0M : (decimal)trip.Surcharge;
                }

                ps_old.TotalDue = ps_old.TotalPrice + ps_old.Surcharge;

                if (ps.TotalDue > ps_old.TotalDue) //need to pay
                {
                    ps.AdultPrice -= ps_old.AdultPrice;
                    ps.ChildPrice -= ps_old.ChildPrice;
                    ps.TotalPrice -= ps.TotalPrice;
                    ps.Surcharge -= ps_old.Surcharge;
                    ps.TotalDue -= ps_old.TotalDue;
                }
                else
                {
                    ps.AdultPrice = 0;
                    ps.ChildPrice = 0;
                    ps.TotalPrice = 0;
                    ps.Surcharge = 0;
                    ps.TotalDue = 0;
                }
            }

            return ps;
        }

        

        /*
         * Accept set(s) of old & new trips (differentiate by ToBeChanged property) and update the info
         * If new total due is bigger, update the prices. Else, only update the flight info but no refund.
         */
        public SaveResult SaveChangedBookings(IEnumerable<FlightBookingTripView> trips)
        {
            if (trips == null || trips.Count() == 0)
            {
                SetError("Booking trips count cannot be null or zero.");
                return new SaveResult()
                {
                    Status = Constants.SaveStatus.Failed,
                    Message = ErrorMessage
                };
            }
            
            IEnumerable<FlightBookingTripView> oldTrips = trips.Where(t => t.ToBeChanged == true).OrderBy(t => t.FlightDate).ThenBy(t => t.DepartureTime);
            IEnumerable<FlightBookingTripView> newTrips = trips.Where(t => t.ToBeChanged != true).OrderBy(t => t.FlightDate).ThenBy(t => t.DepartureTime);

            if (oldTrips == null || oldTrips.Count() == 0)
            {
                SetError("Old trips count cannot be zero.");
                return new SaveResult()
                {
                    Status = Constants.SaveStatus.Failed,
                    Message = ErrorMessage
                };
            }

            if (oldTrips.Count() != newTrips.Count())
            {
                SetError("Old trip(s) and new trip(s) counts not tally.");
                return new SaveResult()
                {
                    Status = Constants.SaveStatus.Failed,
                    Message = ErrorMessage
                };
            }

            //check if seats are still available
            foreach (FlightBookingTripView trip in newTrips)
            {
                if (GetNumOfAvailableSeats(trip) < oldTrips.First().BookingTripDetailViews.Count())
                {
                    SetError("No seats available for flight " + trip.FlightNo + " on " + trip.FlightDate.ToShortDateString() + ".");
                    return new SaveResult()
                    {
                        Status = Constants.SaveStatus.Failed,
                        Message = ErrorMessage
                    };
                }
            }

            try
            {
                //compare the total due amounts
                decimal oldTotalDue = oldTrips.Sum(t => t.TotalPrice + (t.Surcharge == null ? 0.00M : (decimal)t.Surcharge));
                decimal newTotalDue = newTrips.Sum(t => t.TotalPrice + (t.Surcharge == null ? 0.00M : (decimal)t.Surcharge));

                bool isReturnAlso = oldTrips.Count() > 1;
                bool isToSavePrices = newTotalDue > oldTotalDue; //need to pay - update all charges

                //for both updating booking & seats availability
                using (TransactionScope ts = new TransactionScope())
                {

                    using (var ent = new Entities())
                    {
                        FlightBookingTripView oldDepartTrip = oldTrips.First();
                        FlightBookingTripView newDeprtTrip = newTrips.First();

                        //retrieve old booking from EF               
                        FlightBooking fb = ent.FlightBookings.Where(b => b.ReferenceNumber == oldDepartTrip.ReferenceNumber).Single();

                        //update trip(s) info
                        FlightBookingTrip departTrip = fb.FlightBookingTrips.Where(t => t.FlightBookingTripId == oldDepartTrip.FlightBookingTripId).First();
                        ChangeFlightBookingTripInfo(ent, ref departTrip, newDeprtTrip, isToSavePrices);
                        if (isReturnAlso)
                        {
                            FlightBookingTripView oldReturnTrip = oldTrips.Skip(1).First();
                            FlightBookingTripView newReturnTrip = newTrips.Skip(1).First();
                            FlightBookingTrip returnTrip = fb.FlightBookingTrips.Where(t => t.FlightBookingTripId == oldReturnTrip.FlightBookingTripId).First();
                            ChangeFlightBookingTripInfo(ent, ref returnTrip, newReturnTrip, isToSavePrices);
                        }

                        fb.LastUpdatedDateTime = DateTime.Now;
                        fb.LastUpdatedBy = Constants.SYSTEM_ACCOUNT_NAME;
                        if (fb.IsValid)
                        {
                            ent.SaveChanges();
                        }
                        else
                        {
                            return new SaveResult()
                            {
                                Status = Constants.SaveStatus.Failed,
                                Message = fb.ErrorMessage
                            };
                        }

                        //update no. of booked seats for the flight trip
                        SaveResult updateSeatResult = null;
                        foreach (FlightBookingTripView trip in oldTrips)
                        {
                            updateSeatResult = UpdateSeatAvailability(trip, Constants.Operation.Subtract, oldDepartTrip.BookingTripDetailViews.Count());

                            if (updateSeatResult.Status == Constants.SaveStatus.Failed)
                                return updateSeatResult;
                        }

                        foreach (FlightBookingTripView trip in newTrips)
                        {
                            updateSeatResult = UpdateSeatAvailability(trip, Constants.Operation.Add, oldDepartTrip.BookingTripDetailViews.Count());

                            if (updateSeatResult.Status == Constants.SaveStatus.Failed)
                                return updateSeatResult;
                        }
                    }

                    ts.Complete();
                }
            }
            catch(Exception ex)
            {                
                //TODO: log error
                SetError("Exception occurred when saving changed booking.");
                return new SaveResult()
                {
                    Status = Constants.SaveStatus.Failed,
                    Message = ErrorMessage
                };
            }

            return new SaveResult()
            {
                Status = Constants.SaveStatus.Success,
                Message = "Successfully updated the changed booking."
            };
        }        

        /*
         * Change the old booking trip based on the changed info
         */
        private void ChangeFlightBookingTripInfo(Entities ent, ref FlightBookingTrip trip, FlightBookingTripView newTrip, bool isToSavePrices)
        {
            //change depart trip values
            trip.FlightDate = newTrip.FlightDate;
            trip.FlightScheduleId = newTrip.FlightScheduleId == null ? 0 : (long)newTrip.FlightScheduleId;

            if (isToSavePrices)
            {
                foreach (FlightBookingTripDetail dt in trip.FlightBookingTripDetails)
                {
                    if (dt.Passenger.Type == (byte)Constants.PassengerType.Adult)
                    {
                        dt.Price = ent.FlightSchedules.Where(s => s.FlightScheduleId == newTrip.FlightScheduleId).Select(s => s.AdultFare).Single();
                    }
                    else if (dt.Passenger.Type == (byte)Constants.PassengerType.Child)
                    {
                        dt.Price = ent.FlightSchedules.Where(s => s.FlightScheduleId == newTrip.FlightScheduleId).Select(s => s.ChildFare).Single();
                    }

                    dt.Surcharge = ent.FlightSchedules.Where(s => s.FlightScheduleId == newTrip.FlightScheduleId).First().Surcharges.Sum(sc => sc.Price);
                }
            }               
        }

        /*
        * Get number of available seats for a flight (by FlightScheduleId & FlightDate)
        */
        private int GetNumOfAvailableSeats(FlightBookingTripView trip)
        {
            int numSeats = 0;

            using (var ent = new Entities())
            {
                FlightTripStatu tripStatus = ent.FlightTripStatus.Where(ts => ts.FlightScheduleId == trip.FlightScheduleId && ts.FlightDate == trip.FlightDate).FirstOrDefault();

                if (tripStatus != null)
                {
                    numSeats = tripStatus.FlightSchedule.Aircraft.MaxSeat - tripStatus.NoOfBookedSeats;
                }
                else //not found flight status table
                {
                    FlightSchedule fs = ent.FlightSchedules.Where(s => s.FlightScheduleId == trip.FlightScheduleId).SingleOrDefault();
                    if (fs != null)
                    {
                        numSeats = fs.Aircraft.MaxSeat;
                    }
                }
            }

            return numSeats;
        }

        /*
         * Update NoOfBookedSeats field in FlightTripStatus (by using FlightScheduleId and FlightDate)
         * Operations: Add/Subtract
         */
        private SaveResult UpdateSeatAvailability(FlightBookingTripView trip, Constants.Operation operation, int num)
        {
            SaveResult result = new SaveResult();
            int numSeats = 0;
            try
            {
                using (var ent = new Entities())
                {
                    FlightTripStatu tripStatus = ent.FlightTripStatus.Where(ts => ts.FlightScheduleId == trip.FlightScheduleId && ts.FlightDate == trip.FlightDate).FirstOrDefault();

                    if (tripStatus != null)
                    {
                        numSeats = tripStatus.FlightSchedule.Aircraft.MaxSeat - tripStatus.NoOfBookedSeats; //current value
                        numSeats = operation == Constants.Operation.Add ? numSeats + num : numSeats - num; //after op
                        numSeats = numSeats < 0 ? 0 : numSeats;

                        tripStatus.NoOfBookedSeats = numSeats;

                        if (tripStatus.IsValid)
                        {
                            ent.SaveChanges();
                        }
                        else
                        {
                            return new SaveResult()
                            {
                                Status = Constants.SaveStatus.Failed,
                                Message = tripStatus.ErrorMessage
                            };
                        }
                    }
                    else //not found flight status table
                    {
                        FlightSchedule fs = ent.FlightSchedules.Where(s => s.FlightScheduleId == trip.FlightScheduleId).SingleOrDefault();
                        if (fs != null)
                        {                           
                            numSeats = operation == Constants.Operation.Add ? numSeats + num : numSeats; //after op
                            numSeats = numSeats < 0 ? 0 : numSeats;

                            FlightTripStatu newTripStatus = new FlightTripStatu();
                            newTripStatus.FlightScheduleId = Convert.ToInt64(trip.FlightScheduleId);
                            newTripStatus.FlightDate = trip.FlightDate;
                            newTripStatus.NoOfBookedSeats = numSeats;

                            ent.FlightTripStatus.Add(newTripStatus);
                            ent.SaveChanges();
                        }
                    }
                }

                result.Status = Constants.SaveStatus.Success;                
            }
            catch (Exception)
            {
                result.Status = Constants.SaveStatus.Failed;
                result.Message = "Failed updating the seat availability.";                
                return result;
            }

            return result;
        }

        public SaveResult CreateBookings(ReservationViewObject data)
        {
            SaveResult result = new SaveResult();
            
            try
            {
                //Setp 0: Validation 
                //Step 1: Create/Update Passenger
                //Step 2: Insert Into FlightBooking
                //Step 3: Insert Into [dbo].[FlightBookingPassenger]
                //Step 4: Insert into [dbo].[FlightBookingTrip]
                //Step 5: Insert into [dbo].[FlightBookingTripDetail]
                //Step 6: Insert into Customer

                //for both saving booking & updating seats
                using (TransactionScope ts = new TransactionScope())
                {
                    using (var ent = new Entities())
                    {

                        //Setp 0: checked if seats are available
                        //check if seats are still available
                        foreach (FlightBookingTripView trip in data.SelectedBookingTrips)
                        {
                            if (GetNumOfAvailableSeats(trip) < (data.NumOfAdult + data.NumOfChild))
                            {
                                SetError("No seats available for flight " + trip.FlightNo + " on " + trip.FlightDate.ToShortDateString() + ".");
                                return new SaveResult()
                                {
                                    Status = Constants.SaveStatus.Failed,
                                    Message = ErrorMessage
                                };
                            }
                        }

                        //Step 1:
                        //foreach (var passenger in data.Passengers)
                        //{
                        //    var existPassenger = ent.Passengers.Where(x => x.PassportNumber == passenger.PassportNumber).FirstOrDefault();
                        //    if (existPassenger != null)
                        //    {
                        //        existPassenger.FirstName = passenger.FirstName;
                        //        existPassenger.LastName = passenger.LastName;
                        //        existPassenger.Type = passenger.Type;
                        //        existPassenger.DateOfBirth = passenger.DateOfBirth;
                        //        existPassenger.CountryId = passenger.CountryId;
                        //        existPassenger.ExpiryDate = passenger.ExpiryDate;
                        //    }
                        //    else
                        //    {
                        //        ent.Passengers.Add(passenger);
                        //    }
                        //}

                        //Step 2:

                        FlightBooking flightBooking = new FlightBooking();
                        flightBooking.PaymentMode = 1;
                        flightBooking.ReferenceNumber = Guid.NewGuid().ToString();
                        flightBooking.IsCancelled = false;
                        flightBooking.CreatedDateTime = DateTime.Now;
                        flightBooking.CreatedBy = Constants.SYSTEM_ACCOUNT_NAME;
                        flightBooking.LastUpdatedDateTime = DateTime.Now;
                        flightBooking.LastUpdatedBy = Constants.SYSTEM_ACCOUNT_NAME;
                        //if email the same, we trace it as same person
                        var existCustomer = ent.Customers.Where(x => x.FirstName==data.Contact.FirstName && x.LastName==data.Contact.LastName).FirstOrDefault();

                        if (existCustomer != null)
                        {
                            flightBooking.Customer = existCustomer;
                        }
                        else
                        {
                            flightBooking.Customer = data.Contact;
                        }


                        foreach (var passenger in data.Passengers)
                        {
                            var existPassenger = ent.Passengers.Where(x => x.PassportNumber == passenger.PassportNumber).FirstOrDefault();
                            if (existPassenger != null)
                            {
                                passenger.PassengertId = existPassenger.PassengertId;
                                existPassenger.FirstName = passenger.FirstName;
                                existPassenger.LastName = passenger.LastName;
                                existPassenger.Type = passenger.Type;
                                existPassenger.DateOfBirth = passenger.DateOfBirth;
                                existPassenger.CountryId = passenger.CountryId;
                                existPassenger.ExpiryDate = passenger.ExpiryDate;
                                flightBooking.Passengers.Add(existPassenger);

                            }
                            else
                            {
                                flightBooking.Passengers.Add(passenger);

                            }
                        }

                        //Step 3:
                        foreach (var trip in data.SelectedBookingTrips)
                        {
                            var schedule = ent.FlightSchedules.FirstOrDefault(x => x.FlightScheduleId == trip.FlightScheduleId);

                            FlightBookingTrip t = new FlightBookingTrip();
                            t.FlightBooking = flightBooking;
                            t.FlightDate = trip.FlightDate;
                            t.FlightScheduleId = trip.FlightScheduleId;
                            foreach (var passenger in data.Passengers)
                            {
                                FlightBookingTripDetail detail = new FlightBookingTripDetail();
                                var existPassenger = ent.Passengers.Where(x => x.PassportNumber == passenger.PassportNumber).FirstOrDefault();
                                if (existPassenger != null)
                                {
                                    detail.Passenger = existPassenger;
                                }
                                else
                                {
                                    detail.Passenger = passenger;
                                }

                                if (passenger.Type == (byte)Enums.PassengerType.Adult)
                                {
                                    detail.Price = schedule.AdultFare;
                                }
                                else if (passenger.Type == (byte)Enums.PassengerType.Child)
                                {
                                    detail.Price = schedule.ChildFare;
                                }
                                t.FlightBookingTripDetails.Add(detail);
                            }

                            flightBooking.FlightBookingTrips.Add(t);
                        }

                        if (flightBooking.IsValid)
                        {
                            ent.FlightBookings.Add(flightBooking);
                        }
                        else
                        {
                            return new SaveResult()
                            {
                                Status = Constants.SaveStatus.Failed,
                                Message = flightBooking.ErrorMessage
                            };
                        }

                        ent.SaveChanges();

                        //update seat availability
                        SaveResult updateSeatResult = null;
                        foreach (var trip in data.SelectedBookingTrips)
                        {
                            updateSeatResult = UpdateSeatAvailability(trip, Constants.Operation.Add, flightBooking.Passengers.Count());

                            if (updateSeatResult.Status == Constants.SaveStatus.Failed)
                                return updateSeatResult;
                        }

                        result.Status = Constants.SaveStatus.Success;
                        result.Message = flightBooking.ReferenceNumber;
                    }

                    ts.Complete(); 
                }                
            }
            catch (Exception ex)
            {
                //Log Exception
                result.Status = Constants.SaveStatus.Failed;
                //result.Message = "Database Error.";
                result.Message = ex.Message;
            }

            return result;
            
        }

        
    }
}
